using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Collections;
using System.Data;
namespace DAOLayer
{
    /*
     * author Vu Dinh Phong
     */
    public class DocGiaDAO:AbStractDao
    {
        public DocGiaDAO()
        {
            this.connect();
        }
        
        public static DataTable getListDocGia()
        {
            string query = "SELECT * FROM DocGia";
            DocGiaDAO dgDao = new DocGiaDAO();
            DataTable dt = dgDao.fillDataTable(query);
            dgDao.disconnect();           
            return dt;
        }

        public static DataTable getThongTinDocGia(int maDocGia)
        {
            string query = "select dg.maDocGia, dg.hoTen, dg.ngaySinh, dg.dienThoai, dg.hanSuDung, dg.maLop, dg.maHocSinh from Muon m,DocGia dg";
            query += " where m.maDocGia = " + maDocGia;
            query += " and dg.maDocGia = m.maDocGia";
            DocGiaDAO dgDAO = new DocGiaDAO();
            DataTable dt = dgDAO.fillDataTable(query);
            dgDAO.disconnect();

            return dt;
        }

        public static DataTable getDocGiaTheoMaDocGia(int maDocGia)
        {
            string query = "select * from DocGia where maDocGia = " + maDocGia;
            DocGiaDAO dgDAO = new DocGiaDAO();
            DataTable dt = dgDAO.fillDataTable(query);

            dgDAO.disconnect();

            return dt;
        }

        public static DataTable getListDocGiaTheoLop(String maLop)
        {
            string query = "SELECT * FROM DocGia where MaLop = '" + maLop + "'" ;
            DocGiaDAO dgDao = new DocGiaDAO();
            DataTable dt = dgDao.fillDataTable(query);
            dgDao.disconnect();
            return dt;
        }
        public static DataTable getListDocGiaTheoNamHetHan(String nam)
        {
            string query = "SELECT * FROM DocGia where Year(hanSuDung) = '" + nam + "'";
            DocGiaDAO dgDao = new DocGiaDAO();
            DataTable dt = dgDao.fillDataTable(query);
            dgDao.disconnect();
            return dt;
        }

        public static DataSet getListDocGiaTheoTenDocGia(String ten)
        {
            string query = "select * from DocGia where hoTen = '" + ten + "'";
            DocGiaDAO dgDao = new DocGiaDAO();
            //DataTable dt = dgDao.fillDataTable(query);
            DataSet ds = dgDao.executeQuery_ds(query);
            dgDao.disconnect();

            return ds;
        }

        public static DocGiaDTO getDocGia(int madocgia)
        {
            string strQuery = "select * from DocGia where maDocGia= " + madocgia;
            DocGiaDAO dgDao = new DocGiaDAO();
            IDataReader dr = dgDao.executeQuery(strQuery);
            DocGiaDTO dgDto = new DocGiaDTO();
            if (dr.Read())
            {
                dgDto.MaDocGia = dr.GetInt32(0);
                dgDto.HoTen = dr.GetString(1);
                dgDto.NgaySinh = dr.GetDateTime(2);
                dgDto.DienThoai = dr.GetString(3);
                dgDto.HanSuDung = dr.GetDateTime(4);
                dgDto.MaLop = dr.GetString(5);
                dgDto.MaHocSinh = dr.GetString(6);
                
            }
            dgDao.disconnect();
            return dgDto;
        }
        public bool insert(ref DocGiaDTO info)
        {
            //ma_DocGia, hoten, ngaysinh, dienthoai , han_sd, ma_lop, ma_hocsinh
            string insertCommand = "INSERT INTO DocGia(hoTen,ngaySinh,dienThoai,hanSuDung,maLop,maHocSinh) VALUES('" +
                info.HoTen + "', '" +
                info.NgaySinh + "', '" +
                info.DienThoai + "', '" +
                info.HanSuDung + "', '" +
                info.MaLop + "', '" +
                info.MaHocSinh + "')";

            bool flag = executeNonQuery(insertCommand);
         
            return flag;
        }
        public bool update(DocGiaDTO info)
        {
            string updateCommand = "UPDATE DocGia " +
                                    "SET hanSuDung = '" + info.HanSuDung +  "'," +
                                    " hoTen = '" + info.HoTen + "'," +
                                    " ngaySinh = '" + info.NgaySinh + "'," +
                                    " dienThoai = '" + info.DienThoai + "'," +
                                    " maLop = '" + info.MaLop + "'," +
                                    " maHocSinh = '" + info.MaHocSinh + "'" +
                                    " WHERE MaDocGia = " + info.MaDocGia ;
            bool flag = this.executeNonQuery(updateCommand);
    
            return flag;
        }
        public bool delete(int madocgia)
        {
            string deleteCommand = "DELETE FROM DocGia WHERE MaDocGia = " + madocgia;
            bool flag = this.executeNonQuery(deleteCommand);
            return flag;
        }

        // An them vao phan sau de lam phan bao cao thong ke

        public float getTyLeDocGiaMuonSach()
        {
            DocGiaDAO dgDao = new DocGiaDAO();
            string query = "Select count(*) from DocGia";
            string strQuery = "SELECT count(*) FROM DocGia where DocGia.MaDocGia in (Select Muon.MaDocGia from Muon)";

            long SoDocGiaMuonSach = 0;
            long SoDocGia = 0;
            float Tyle = 0;

            IDataReader dr = dgDao.executeQuery(strQuery);
            if (dr.Read())
                SoDocGiaMuonSach = dr.GetInt32(0);

            IDataReader dr1 = dgDao.executeQuery(query);
            if (dr1.Read())
                SoDocGia = dr1.GetInt32(0);

            if (SoDocGia != 0)
                Tyle = SoDocGiaMuonSach / SoDocGia * 100;

            dgDao.disconnect();
            return Tyle;
        }

        public long getSoLuongSachNhieuNhat()
        {
            DocGiaDAO dgDao = new DocGiaDAO();
            string strQuery = "SELECT count(*) FROM DocGia where DocGia.MaDocGia in (Select Muon.MaDocGia from Muon) Group by MaDocGia Order by MaDocGia desc";
            IDataReader dr = dgDao.executeQuery(strQuery);
            long SoLuongSachNhieuNhat = 0;
            if (dr.Read())
                SoLuongSachNhieuNhat = dr.GetInt32(0);
            dgDao.disconnect();
            return SoLuongSachNhieuNhat;
        }
        public DataTable getListDocGiaBaoCao()
        {
            //string query = "SELECT * FROM DocGia";            
            string query = "SELECT DocGia.maDocGia, hoten, ngaysinh, dienthoai , hansudung, malop, mahocsinh, count(mamuonsach) as sosachdamuon FROM DocGia left join Muon on DocGia.madocgia = Muon.madocgia group by mamuonsach, DocGia.maDocGia, hoten, ngaysinh, dienthoai , hansudung, malop, mahocsinh order by docgia.madocgia";
            DocGiaDAO dgDao = new DocGiaDAO();
            DataTable dt = dgDao.fillDataTable(query);
            dgDao.disconnect();
            return dt;
        }
    }
}
